import xlrd,xlwt
from xlutils.copy import copy
import os 
class Excel(object):
    def __init__(self, path):
        self.path = path
    def find_data(self, sname):
        find_rd=xlrd.open_workbook(self.path)
        sheet=find_rd.sheet_by_name(sname)
        for i in range(sheet.nrows):
            values=sheet.row_values(i)
            for j in values:
                print(j,end=' '*6)
            print('')

    #excel的添加,传入参数:你要添加的数据—列表格式
    def add_data(self,slist):
        add_wt=xlrd.open_workbook(self.path)
        row=add_wt.sheets()[0].nrows
        new_add_wt=copy(add_wt)
        sheet=new_add_wt.get_sheet(0)
        i=0
        for j in slist:
            sheet.write(row,i,j)
            i+=1
        new_add_wt.save(self.path)

    #excel指定删除某一行数据,传入参数:表sheet的名字，ID字段
    def delete_data(self,sname,goods_id):
        list_delete=[]
        delete_rd=xlrd.open_workbook(self.path)
        sheet=delete_rd.sheet_by_name(sname)
        for i in range(sheet.nrows):
            values=sheet.row_values(i)
            for j in values:
                if j==goods_id:
                    print('删除成功')
                else:
                    list_delete.append(values)
                break
        if sheet.nrows==len(list_delete):
            print('没有该商品')
        wbt = xlwt.Workbook()
        sheet = wbt.add_sheet(sname)
        for m in range(len(list_delete)):
            for n in range(len(list_delete[m])):
                sheet.write(m, n, list_delete[m][n])
        wbt.save(self.path)
        
    def delete_all_data(self,sname,slist):
        wbt = xlwt.Workbook()
        sheet = wbt.add_sheet(sname)
        i=0
        for j in slist:
            sheet.write(0,i,j)
            i+=1
        wbt.save(self.path)


    #excel指定修改某一行数据 第一个参数是sheet的名字，第二个参数是要修改数据的ID号，第三个参数是修改后的列表
    def update_data(self,sname,goods_id,slist):
        wb = xlrd.open_workbook(self.path)
        sheet = wb.sheet_by_name(sname)  
        new_wb = copy(wb)  
        new_sheet = new_wb.get_sheet(0)
        list_col=sheet.col_values(0)
        if goods_id in list_col:
            row=list_col.index(goods_id)
            i=0
            for j in slist:
                new_sheet.write(row,i,j)
                i+=1
        else:
            print('没有搜索到该信息')
        new_wb.save(self.path)

    def returnlist(self,sheetname):
        wb = xlrd.open_workbook(self.path)
        sheet = wb.sheet_by_name(sheetname)
        msy = [] 
        for rownum in range(sheet.nrows):
            msg = []
            for colnum in range(sheet.ncols):
                msg.append(str(sheet.cell(rownum,colnum).value))
            msy.append(msg)
        return msy


if __name__ == "__main__":
    d = os.path.dirname(__file__)
    parent_path = os.path.dirname(d)
    file_path = parent_path + '/data/goods_info.xls'
    excel = Excel(file_path)
    # list_goods=['11356','华为手机','1199','40','22222']
    # excel.add_data(list_goods)
    # excel.delete_data('商品信息','11236')
    #excel.find_data('商品信息')
    # list_goods=['11237', '海尔电脑', '6666', '60', '11111']
    # excel.update_data('商品信息','11237',list_goods)
    # goods_title = ['商品ID','商品名称','商品价格','商品数量','商家ID']
    # excel.delete_all_data('商品信息',goods_title)

        